--EXEC SP_SAVE_DIEBANK_DATA_ENTRY_BINNING_INFO 5,105,'DB_BIN_LOT4','5',4000,'BIN1',0,0.0

ALTER PROC SP_SAVE_DIEBANK_DATA_ENTRY_BINNING_INFO 
(
	@PROCESSSTEP_ID	INT,
	@LOCATION_ID	INT,
	@LOT_NUMBER 	VARCHAR(50),
	@WAFER_ID       VARCHAR(50),
	@BIN_QTY		INT,
	@BIN_NUMBER		VARCHAR(50),
	@REJECTED_QTY	INT,
	@YIELD		FLOAT
		
)
AS
BEGIN
 	
	DECLARE @LOT_ID INT
	
	DECLARE @SORT_COLUMNSTRING NVARCHAR(100)
	DECLARE @INFO_COLUMNSTRING NVARCHAR(100)
	DECLARE @PARAMDEFINITION NVARCHAR(100)
	DECLARE @SORT_QUERYSTRING NVARCHAR(200)
	DECLARE @INFORMATIVE_QUERYSTRING NVARCHAR(200)
	DECLARE @SELECT_STRING NVARCHAR(200)
	DECLARE @BIN_COLUMN_NO VARCHAR(50)
	DECLARE @BIN_BALENCE_NO VARCHAR(50)	

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	--PRINT @LOT_ID
	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'			
	END
	ELSE
	BEGIN
		IF ( SELECT COUNT(*) FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID )  = 0
		BEGIN
			
			SET @BIN_COLUMN_NO = REPLACE(@BIN_NUMBER, 'BIN','BIN_')	
	
			SET @BIN_BALENCE_NO = REPLACE(@BIN_COLUMN_NO, 'BIN','BIN')+'_BALANCE_QTY'
			
			SET @SORT_QUERYSTRING = N'INSERT INTO SORT_BIN_RELEASE_TABLE (LOT_ID, WAFER_ID, '+@BIN_COLUMN_NO + ', '+@BIN_BALENCE_NO + ') 
				VALUES('+CONVERT(NVARCHAR, @LOT_ID)+','+CONVERT(NVARCHAR,@WAFER_ID)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@BIN_QTY)+')'
			
			SET @INFORMATIVE_QUERYSTRING = N'INSERT INTO INFORMATIVE_BINNING (LOT_ID, WAFER_ID, GOOD_DICE, YIELD, '+@BIN_COLUMN_NO + ', '+'BIN_REJECTED)
 				VALUES('+CONVERT(NVARCHAR,@LOT_ID)+','+CONVERT(NVARCHAR,@WAFER_ID)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@YIELD)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@REJECTED_QTY)+')'
									
			EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING  -- INSERTS INTO INFORMATIVE BINNING

            IF ( SELECT COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID )  = 0
            BEGIN
			    EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING
            END
			ELSE
			BEGIN
				IF ISNULL(@BIN_QTY, 0) > 0
				BEGIN
					SET @SORT_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , '+ CONVERT(NVARCHAR,@BIN_BALENCE_NO) +' = ' + CONVERT(NVARCHAR,@BIN_QTY)
				END
				ELSE
				BEGIN
					SET @SORT_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_BALENCE_NO) +' = ' + CONVERT(NVARCHAR,@BIN_QTY)
				END 			

				SET @SORT_QUERYSTRING = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@SORT_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
				SET @PARAMDEFINITION = N'@LOT_ID INT,@WAFER_ID INT'
				EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID	
			END
			
			SET @SORT_QUERYSTRING = null
			SET @INFORMATIVE_QUERYSTRING = null
			SET @BIN_BALENCE_NO = null
			SET @BIN_COLUMN_NO  = null
		END
		ELSE
		BEGIN		
			--PRINT @BIN_NUMBER
			SET @BIN_COLUMN_NO = REPLACE(@BIN_NUMBER, 'BIN','BIN_')	
			SET @BIN_BALENCE_NO = REPLACE(@BIN_COLUMN_NO, 'BIN','BIN')+'_BALANCE_QTY'
			IF ISNULL(@BIN_QTY, 0) > 0
			BEGIN
				SET @SORT_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , '+ CONVERT(NVARCHAR,@BIN_BALENCE_NO) +' = ' + CONVERT(NVARCHAR,@BIN_QTY)
			END
			ELSE
			BEGIN
				SET @SORT_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_BALENCE_NO) +' = ' + CONVERT(NVARCHAR,@BIN_QTY)
			END 			
			
			SET @INFO_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , YIELD = ' + CONVERT(NVARCHAR,@YIELD)
			SET @SORT_QUERYSTRING = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@SORT_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
			SET @INFORMATIVE_QUERYSTRING = N'UPDATE INFORMATIVE_BINNING SET '+@INFO_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
			SET @PARAMDEFINITION = N'@LOT_ID INT,@WAFER_ID INT'
						
			
			EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID
			EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID	
						
			SET @SORT_QUERYSTRING = null
			SET @INFORMATIVE_QUERYSTRING = null
			SET @BIN_BALENCE_NO = null
			SET @BIN_COLUMN_NO  = null
			
		END
	END
END